MySQLでNOT NULLかつデフォルト値を指定しているカラムへのNULLでのUPDATE時の挙動と厳格モードによる挙動の変化
ローカルのMySQLでは特定のカラムアップデート時にエラーが発生するが、他の環境ではエラーが発生しないということが起きました。原因が分かるまで少し手こずったので、その内容と再現方法を書き残しておきます。
エラーとその解決方法
状況
NOT NULLかつデフォルト値を設定したTINYINTカラム(カラムAとする)を含むテーブルに対して、カラムAに対応した値にNULLを設定して1行アップデートを実行。環境Aでは実行成功し、環境Bではエラーが出て実行に失敗。
- 環境A: Aurora MySQL 5.7.12互換
- sql_mode: 0(モードの設定なし)
- 環境B: MySQL 5.7.30 (mysql: 5.7)
- sql_mode: STRICT_TRANS_TABLESを含む、複数のモードが設定
エラー内容
ERROR 1048 (23000): Column 'カラム名' cannot be null
解決方法
環境Bのsql_modeからSTRICT_TRANS_TABLESとSTRICT_ALL_TABLESを除くことでエラーを抑制し、UPDATEを実行できるようにできます。
例えば、次のようなsql_modeが設定されている場合...
SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+ | @@global.sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
次のようにSTRICT_TRANS_TABLES以外のモードを設定することで、今回のエラーを抑制できます。
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
暗黙的な値の変換やUPDATE時のデフォルト値の使用はバグを産む可能性を増大させます。可能な限り、適切な値でのINSERTやUPDATEを行うことをお勧めします。
解説
MySQLではsql_modeを設定することで、クエリ実行時の細かな挙動を変更することができます。そのsql_modeに設定可能なモードには、STRICT_ALL_TABLESやSTRICT_TRANS_TABLESが含まれており、これらを有効化することで厳格(strict)モードでクエリが実行されます。厳格モードによる主な効果は、INSERTやUPDATEなどでテーブルにデータを挿入したり変更する際に、値が対象のカラムの定義と異なっている場合、エラーが発生するようになることです。厳格モードではない場合、警告(warning)を出しながらも暗黙的な値の変換が行われて挿入や変更が実行されます。型が異なっている場合だけでなく、NOT NULLかつデフォルト値が設定されているカラムに対してNULLでUPDATEする際も同様です。厳格モードでない場合は警告を出しながらもデフォルト値でUPDATEされ、厳格モードの場合は、エラーが発生します。
今回のケースでは、NOT NULLかつデフォルト値が定義されていたカラムに対して、NULLでUPDATEしようとしていました。なので、厳格モードの環境Bではエラーになり、厳格モードではない環境Aでは警告が出つつもデフォルト値でUPDATEを実行という結果になった次第です。
ちなみに、STRICT_ALL_TABLESとSTRICT_TRANS_TABLESの違いは、非トランザクションかつ複数行に対するクエリ実行時の二行目以降でカラム定義と異なる値の挿入や操作が発生した際の挙動です。
STRICT_TRANS_TABLES: 非厳格モードと同様に警告を出しつつそのままクエリが実行されます。STRICT_ALL_TABLES: エラーが発生し、以降の行に対する処理は実行されません。エラーが発生するまでの行のみ、実行されます。
試してみる
Dockerイメージmysql: 5.7を使ってMySQLサーバーを作成し、厳格モードと非厳格モードでのNOT NULLかつデフォルト値を設定したカラムに対するNULLでのUPDATEを試してみます。
準備
まずはDockerでMySQLサーバ用コンテナを起動させます。
docker run --name sample_mysql --env MYSQL_ALLOW_EMPTY_PASSWORD=1 mysql:5.7
コンテナが立ち上がったら、MySQLサーバに接続します。
docker exec -it sample_mysql mysql -uroot
ここからはSQLクエリを書いていきます。 まずは作業用にDBを作成します。
CREATE DATABASE sample; USE sample;
検証用にいじくるためのテーブルを作成します。
CREATE TABLE sample_table (
id INT(8) NOT NULL,
column1 TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);
column1の値のUPDATE操作を検証するためにレコードを入れておきます。
INSERT INTO sample_table VALUES (1, 1);
検証
動作を検証する前にsql_modeを確認してみます。
SELECT @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+ | @@SESSION.sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
STRICT_TRANS_TABLESが含まれていることが確認できます。厳格モードでクエリは実行されるようです。
NULLでのUPDATEを試してみます。
UPDATE sample_table SET column1=NULL WHERE id=1;
ERROR 1048 (23000): Column 'column1' cannot be null
案の定エラーになりました。
sql_modeにSTRICT_TRANS_TABLES以外のモードを設定し、厳格モードを解除します。
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
再度UPDATEを試してみます。
UPDATE sample_table SET column1=NULL WHERE id=1;
Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1
今回は成功しました。警告が出ているので、内容を確認します。
SHOW WARNINGS;
+---------+------+---------------------------------+ | Level | Code | Message | +---------+------+---------------------------------+ | Warning | 1048 | Column 'column1' cannot be null | +---------+------+---------------------------------+ 1 row in set (0.00 sec)
先ほどエラーだった内容が、そのまま警告になっています。
UPDATEしたデータを確認してみます。
SELECT * FROM sample_table;
+----+---------+ | id | column1 | +----+---------+ | 1 | 0 | +----+---------+ 1 row in set (0.00 sec)
1だったcolumn1がデフォルト値である、0になっているのが分かります。
さいごに
MySQLでNOT NULLかつデフォルト値を指定しているカラムに対してNULLでUPDATEした際の挙動について紹介しました。厳格モードでない場合、エラーが出ないためアプリケーションは問題なく動きます。しかし、元の値からデフォルト値に更新されるのが意図的でない場合、発見の難しいバグを産み出すことになってしまうため、注意が必要です。
